<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- saved from url=(0060)http://www.postgresql.org/docs/9.1/static/libpq-example.html -->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en" dir="ltr"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>PostgreSQL: Documentation: 9.1: Example Programs</title>
    <style type="text/css" media="screen" title="Normal Text">@import url("/dyncss/docs.css?35336e31");</style>
    <script type="text/javascript" async="" src="./PostgreSQL  Documentation  9.1  Example Programs_files/ga.js"></script><script type="text/javascript" src="./PostgreSQL  Documentation  9.1  Example Programs_files/monospacefix.js"></script></head><body><pre id="monotest" style="display: none;">&nbsp;</pre><p id="paratest" style="display: none;">&nbsp;</p>
    <script type="text/javascript">
  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-1345454-1']);
  _gaq.push(['_trackPageview']);
  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();
</script>


<div id="docHeader">
	<div id="docHeaderLogo">
		<a href="http://www.postgresql.org/" title="PostgreSQL"><img src="./PostgreSQL  Documentation  9.1  Example Programs_files/hdr_logo.png" width="206" height="80" alt="PostgreSQL"></a>
	</div>
</div>

<div id="docContainerWrap">
<div id="docContainer">
<div id="docNavSearchContainer">
<div id="docSearch">
	<form action="http://www.postgresql.org/search/" method="get">
	<div>
	<input type="hidden" name="u" value="/docs/9.1/">
	<label for="q">Search Documentation:&nbsp;</label><input type="text" id="q" name="q" size="20" onfocus="if( this.value==this.defaultValue ) this.value=&#39;&#39;;" value="Search"><input id="submit" type="submit" value="Search">
	</div>
	</form>
</div>
<div id="docNav">
<a href="http://www.postgresql.org/" title="Home">Home</a> → <a href="http://www.postgresql.org/docs" title="Documentation">Documentation</a> → <a href="http://www.postgresql.org/docs/manuals" title="Manuals">Manuals</a> → <a href="http://www.postgresql.org/docs/9.1/static/index.html">PostgreSQL 9.1</a>
</div>
<div id="docVersions">
This page in other versions:


	
	
	<a href="http://www.postgresql.org/docs/9.0/static/libpq-example.html" title="This page in version 9.0">9.0</a>
	

	/
	
	<b>9.1</b>
	

	/
	
	<a href="http://www.postgresql.org/docs/9.2/static/libpq-example.html" title="This page in version 9.2">9.2</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/9.3/static/libpq-example.html" title="This page in version 9.3">9.3</a>
	




&nbsp;|&nbsp; Development versions:

	
	
	<a href="http://www.postgresql.org/docs/devel/static/libpq-example.html" title="This page in version devel" rel="nofollow">devel</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/9.4/static/libpq-example.html" title="This page in version 9.4" rel="nofollow">9.4</a>
	




&nbsp;|&nbsp; Unsupported versions:

	
	
	<a href="http://www.postgresql.org/docs/7.1/static/libpq-example.html" title="This page in version 7.1" rel="nofollow">7.1</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/7.2/static/libpq-example.html" title="This page in version 7.2" rel="nofollow">7.2</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/7.3/static/libpq-example.html" title="This page in version 7.3" rel="nofollow">7.3</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/7.4/static/libpq-example.html" title="This page in version 7.4" rel="nofollow">7.4</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/8.0/static/libpq-example.html" title="This page in version 8.0" rel="nofollow">8.0</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/8.1/static/libpq-example.html" title="This page in version 8.1" rel="nofollow">8.1</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/8.2/static/libpq-example.html" title="This page in version 8.2" rel="nofollow">8.2</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/8.3/static/libpq-example.html" title="This page in version 8.3" rel="nofollow">8.3</a>
	

	/
	
	<a href="http://www.postgresql.org/docs/8.4/static/libpq-example.html" title="This page in version 8.4" rel="nofollow">8.4</a>
	


</div>
</div>

<div id="docContent">
<div class="NAVHEADER">
  <table summary="Header navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
    <tbody><tr>
      <th colspan="5" align="center" valign="bottom"><a href="http://www.postgresql.org/docs/9.1/static/index.html">PostgreSQL 9.1.14 Documentation</a></th>
    </tr>

    <tr>
      <td width="10%" align="left" valign="top"><a title="Building libpq Programs" href="http://www.postgresql.org/docs/9.1/static/libpq-build.html" accesskey="P">Prev</a></td>

      <td width="10%" align="left" valign="top"><a href="http://www.postgresql.org/docs/9.1/static/libpq.html" accesskey="U">Up</a></td>

      <td width="60%" align="center" valign="bottom">Chapter 31.
      <span class="APPLICATION">libpq</span> - C Library</td>

      <td width="20%" align="right" valign="top"><a title="Large Objects" href="http://www.postgresql.org/docs/9.1/static/largeobjects.html" accesskey="N">Next</a></td>
    </tr>
  </tbody></table>
  <hr class="c1" width="100%">
</div>

<div class="SECT1">
  <h1 class="SECT1"><a name="LIBPQ-EXAMPLE" id="LIBPQ-EXAMPLE">31.20. Example Programs</a></h1>

  <p>These examples and others can be found in the directory
  <tt class="FILENAME">src/test/examples</tt> in the source code
  distribution.</p>

  <div class="EXAMPLE">
    <a name="LIBPQ-EXAMPLE-1" id="LIBPQ-EXAMPLE-1"></a>

    <p class="c2">Example 31-1. <span class="APPLICATION">libpq</span> Example Program 1</p>
    <pre class="PROGRAMLISTING">/*
 * testlibpq.c
 *
 *      Test the C version of libpq, the PostgreSQL frontend library.
 */
#include &lt;stdio.h&gt;
#include &lt;stdlib.h&gt;
#include &lt;libpq-fe.h&gt;

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    int         nFields;
    int         i,
                j;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc &gt; 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * Should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /*
     * Fetch rows from pg_database, the system catalog of databases
     */
    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i &lt; nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (i = 0; i &lt; PQntuples(res); i++)
    {
        for (j = 0; j &lt; nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}
</pre>
  </div>

  <div class="EXAMPLE">
    <a name="LIBPQ-EXAMPLE-2" id="LIBPQ-EXAMPLE-2"></a>

    <p class="c2">Example 31-2. <span class="APPLICATION">libpq</span> Example Program 2</p>
    <pre class="PROGRAMLISTING">/*
 * testlibpq2.c
 *      Test of the asynchronous notification interface
 *
 * Start this program, then from psql in another window do
 *   NOTIFY TBL2;
 * Repeat four times to get this program to exit.
 *
 * Or, if you want to get fancy, try this:
 * populate a database with the following commands
 * (provided in src/test/examples/testlibpq2.sql):
 *
 *   CREATE TABLE TBL1 (i int4);
 *
 *   CREATE TABLE TBL2 (i int4);
 *
 *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
 *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
 *
 * and do this four times:
 *
 *   INSERT INTO TBL1 VALUES (10);
 */
#include &lt;stdio.h&gt;
#include &lt;stdlib.h&gt;
#include &lt;string.h&gt;
#include &lt;errno.h&gt;
#include &lt;sys/time.h&gt;
#include &lt;libpq-fe.h&gt;

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    PGnotify   *notify;
    int         nnotifies;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc &gt; 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * Issue LISTEN command to enable notifications from the rule's NOTIFY.
     */
    res = PQexec(conn, "LISTEN TBL2");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /* Quit after four notifies are received. */
    nnotifies = 0;
    while (nnotifies &lt; 4)
    {
        /*
         * Sleep until something happens on the connection.  We use select(2)
         * to wait for input, but you could also use poll() or similar
         * facilities.
         */
        int         sock;
        fd_set      input_mask;

        sock = PQsocket(conn);

        if (sock &lt; 0)
            break;              /* shouldn't happen */

        FD_ZERO(&amp;input_mask);
        FD_SET(sock, &amp;input_mask);

        if (select(sock + 1, &amp;input_mask, NULL, NULL, NULL) &lt; 0)
        {
            fprintf(stderr, "select() failed: %s\n", strerror(errno));
            exit_nicely(conn);
        }

        /* Now check for input */
        PQconsumeInput(conn);
        while ((notify = PQnotifies(conn)) != NULL)
        {
            fprintf(stderr,
                    "ASYNC NOTIFY of '%s' received from backend PID %d\n",
                    notify-&gt;relname, notify-&gt;be_pid);
            PQfreemem(notify);
            nnotifies++;
        }
    }

    fprintf(stderr, "Done.\n");

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}
</pre>
  </div>

  <div class="EXAMPLE">
    <a name="LIBPQ-EXAMPLE-3" id="LIBPQ-EXAMPLE-3"></a>

    <p class="c2">Example 31-3. <span class="APPLICATION">libpq</span> Example Program 3</p>
    <pre class="PROGRAMLISTING">/*
 * testlibpq3.c
 *      Test out-of-line parameters and binary I/O.
 *
 * Before running this, populate a database with the following commands
 * (provided in src/test/examples/testlibpq3.sql):
 *
 * CREATE TABLE test1 (i int4, t text, b bytea);
 *
 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
 *
 * The expected output is:
 *
 * tuple 0: got
 *  i = (4 bytes) 1
 *  t = (11 bytes) 'joe's place'
 *  b = (5 bytes) \000\001\002\003\004
 *
 * tuple 0: got
 *  i = (4 bytes) 2
 *  t = (8 bytes) 'ho there'
 *  b = (5 bytes) \004\003\002\001\000
 */
#include &lt;stdio.h&gt;
#include &lt;stdlib.h&gt;
#include &lt;string.h&gt;
#include &lt;sys/types.h&gt;
#include &lt;libpq-fe.h&gt;

/* for ntohl/htonl */
#include &lt;netinet/in.h&gt;
#include &lt;arpa/inet.h&gt;


static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

/*
 * This function prints a query result that is a binary-format fetch from
 * a table defined as in the comment above.  We split it out because the
 * main() function uses it twice.
 */
static void
show_binary_results(PGresult *res)
{
    int         i,
                j;
    int         i_fnum,
                t_fnum,
                b_fnum;

    /* Use PQfnumber to avoid assumptions about field order in result */
    i_fnum = PQfnumber(res, "i");
    t_fnum = PQfnumber(res, "t");
    b_fnum = PQfnumber(res, "b");

    for (i = 0; i &lt; PQntuples(res); i++)
    {
        char       *iptr;
        char       *tptr;
        char       *bptr;
        int         blen;
        int         ival;

        /* Get the field values (we ignore possibility they are null!) */
        iptr = PQgetvalue(res, i, i_fnum);
        tptr = PQgetvalue(res, i, t_fnum);
        bptr = PQgetvalue(res, i, b_fnum);

        /*
         * The binary representation of INT4 is in network byte order, which
         * we'd better coerce to the local byte order.
         */
        ival = ntohl(*((uint32_t *) iptr));

        /*
         * The binary representation of TEXT is, well, text, and since libpq
         * was nice enough to append a zero byte to it, it'll work just fine
         * as a C string.
         *
         * The binary representation of BYTEA is a bunch of bytes, which could
         * include embedded nulls so we have to pay attention to field length.
         */
        blen = PQgetlength(res, i, b_fnum);

        printf("tuple %d: got\n", i);
        printf(" i = (%d bytes) %d\n",
               PQgetlength(res, i, i_fnum), ival);
        printf(" t = (%d bytes) '%s'\n",
               PQgetlength(res, i, t_fnum), tptr);
        printf(" b = (%d bytes) ", blen);
        for (j = 0; j &lt; blen; j++)
            printf("\\%03o", bptr[j]);
        printf("\n\n");
    }
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    const char *paramValues[1];
    int         paramLengths[1];
    int         paramFormats[1];
    uint32_t    binaryIntVal;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc &gt; 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * The point of this program is to illustrate use of PQexecParams() with
     * out-of-line parameters, as well as binary transmission of data.
     *
     * This first example transmits the parameters as text, but receives the
     * results in binary format.  By using out-of-line parameters we can
     * avoid a lot of tedious mucking about with quoting and escaping, even
     * though the data is text.  Notice how we don't have to do anything
     * special with the quote mark in the parameter value.
     */

    /* Here is our out-of-line parameter value */
    paramValues[0] = "joe's place";

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE t = $1",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       NULL,    /* don't need param lengths since text */
                       NULL,    /* default to all text params */
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    show_binary_results(res);

    PQclear(res);

    /*
     * In this second example we transmit an integer parameter in binary
     * form, and again retrieve the results in binary form.
     *
     * Although we tell PQexecParams we are letting the backend deduce
     * parameter type, we really force the decision by casting the parameter
     * symbol in the query text.  This is a good safety measure when sending
     * binary parameters.
     */

    /* Convert integer value "2" to network byte order */
    binaryIntVal = htonl((uint32_t) 2);

    /* Set up parameter arrays for PQexecParams */
    paramValues[0] = (char *) &amp;binaryIntVal;
    paramLengths[0] = sizeof(binaryIntVal);
    paramFormats[0] = 1;        /* binary */

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE i = $1::int4",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       paramLengths,
                       paramFormats,
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    show_binary_results(res);

    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}
</pre>
  </div>
</div>

<div class="NAVFOOTER">
  <hr class="c1" width="100%">

  <table summary="Footer navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
    <tbody><tr>
      <td width="33%" align="left" valign="top"><a href="http://www.postgresql.org/docs/9.1/static/libpq-build.html" accesskey="P">Prev</a></td>

      <td width="34%" align="center" valign="top"><a href="http://www.postgresql.org/docs/9.1/static/index.html" accesskey="H">Home</a></td>

      <td width="33%" align="right" valign="top"><a href="http://www.postgresql.org/docs/9.1/static/largeobjects.html" accesskey="N">Next</a></td>
    </tr>

    <tr>
      <td width="33%" align="left" valign="top">Building
      <span class="APPLICATION">libpq</span> Programs</td>

      <td width="34%" align="center" valign="top"><a href="http://www.postgresql.org/docs/9.1/static/libpq.html" accesskey="U">Up</a></td>

      <td width="33%" align="right" valign="top">Large Objects</td>
    </tr>
  </tbody></table>
</div>

</div>

<div id="docComments">



</div>

<div id="docFooter">
    <a href="http://www.postgresql.org/about/privacypolicy">Privacy Policy</a> |
    <a href="http://www.postgresql.org/about/">About PostgreSQL</a><br>
Copyright © 1996-2014 The PostgreSQL Global Development Group
</div>
</div>
</div>


</body></html>